<?php
/**
 * Created by PhpStorm.
 * User: Yieong
 * Date: 14/11/19
 * Time: 下午8:12
 */

class ModServer extends Model {

    protected $table = 'server';

    // 表名
    public function tableName(){
        return $this->table;
    }

    public static function model($className=__CLASS__){
        return parent::model($className);
    }

    public function getDbConnection(){
        return Yii::app()->dbOss;
    }
    
    public function page($params){
    	$table = $this->table;
        $where  = ' WHERE platform=:platform AND game=:game ';
        $args   = array(
            ':platform' => 'test',
            ':game'     => $params['g']
        );

        if(!empty($params['server_name'])){
            $where .= ' AND server_name LIKE :name ';
            $args[':name'] = '%'.$params['server_name'].'%';
        }

        if(!empty($params['show_status'])){
            $where .= ' AND show_status = :show_status ';
            $args[':show_status'] = $params['show_status'];
        }
        
        if(!empty($params['status'])){
        	$where .= ' AND status = :status ';
        	$args[':status'] = $params['status'];
        }
        
        if(!empty($params['special_id'])){
        	$where .= ' AND special_id = :special_id ';
        	$args[':special_id'] = $params['special_id'];
        }
        
        if($params['begin'] && $params['end']){
        	$begin = strtotime($params['begin'] . ' 00:00:00');
        	$end = strtotime($params['end'] . ' 23:59:59');
        	$where  .= " AND open_time BETWEEN ".$begin. " AND  ".$end;
        }
        
        if($params['begin'] != '' && $params['end'] == ""){
        	$begin = strtotime($params['begin'] . ' 00:00:00');
        	$end = time();
        	$where  .= " AND open_time BETWEEN ".$begin. " AND  ".$end;
        }
        
        if($params['begin'] == '' && $params['end'] != ""){
        	$params['begin'] = date('Y-m-01');
        	$begin = strtotime($params['begin'] . ' 00:00:00');
        	$end = strtotime($params['end'] . ' 23:59:59');
        	$where  .= " AND open_time BETWEEN ".$begin. " AND  ".$end;
        }
        
    
    	$c_sql = "select count(1) as cnt from {$table} as s LEFT JOIN special_servicer as ss ON ss.server_id = s.id {$where}";
    	$c_result = $this->getDbConnection()->query($c_sql, $args);
    	$count = isset($c_result[0]['cnt']) ? $c_result[0]['cnt'] : 0;
    
    	$order  = self::getOrder($params['order']);
    	$limit  = PubFunc::getLimit($params['length'], $params['page']);
    	$fields = "s.*,ss.special_id";
    	$sql = "SELECT {$fields} FROM {$table} as s LEFT JOIN special_servicer as ss ON ss.server_id = s.id  {$where} {$order} {$limit}";
    	$data = $this->getDbConnection()->query($sql, $args);
    	$result = array('count'=>$count,'data'=>$data);
    	return $result;
    }
    
    public static function getOrder($order){
    	if(PubFunc::checkEmpty($order, array(0, 1))) return '';
    	if(!in_array($order[1], array('asc', 'desc'))){
    		$sort = 'asc';
    	}else{
    		$sort = $order[1];
    	}
    	return " ORDER BY {$order[0]} {$sort} ";
    }

    public function select($params){
        $where  = ' WHERE platform=:platform AND game=:game ';
        $args   = array(
            ':platform' => 'test',
            ':game'     => $params['g']
        );

        if(!empty($params['server_name'])){
            $where .= ' AND server_name LIKE :name ';
            $args[':name'] = '%'.$params['server_name'].'%';
        }

        if(!empty($params['status'])){
            $where .= ' AND status = :status ';
            $args[':status'] = $params['status'];
        }

        $sql = "SELECT * FROM {$this->table} {$where} ORDER BY create_time DESC";

        $result = $this->getDbConnection()->getAll($sql, $args);
        return $result;
    }
    
    /**
     * 根据服务器id获取渠道
     * @param unknown $id
     */
    public function getChannel($id){
    	$sql = "SELECT s.id,s.server_name,sc.channel_id,c.channel_name FROM server as s INNER JOIN server_channel as sc ON sc.server_id = s.id INNER JOIN channel as c on c.id = sc.channel_id WHERE s.id ={$id}";
    	
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    	
    }
    
    public function repeat($id,$field,$val,$g){
    	$sql = "SELECT count(1) as cnt FROM {$this->table} where id != {$id} and {$field} = '{$val}' and game = '{$g}'";
    	$c_result = $this->getDbConnection()->getAll($sql, $args);
    	$count = isset($c_result[0]['cnt']) ? $c_result[0]['cnt'] : 0;
    	return $count;
    }
    
    /**
     * 查询服务器跟专服
     */
    public function getSpecialServer($status){
    	$where = '';
    	if($status != ''){
    		$where  .= " WHERE status={$status}";
    	}
    	$sql = "SELECT ss.id,ss.name,sse.server_id,s.server_name,s.develop_id,s.server_num,s.status from special_server as ss ";
    	$sql .= " INNER JOIN special_servicer as sse ON sse.special_id = ss.id ";
    	$sql .= " INNER JOIN server as s ON s.id = sse.server_id ";
    	$sql .= $where;
    	 
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    	
    }
    
    /**
     * 根据$special_id获取服务器
     * @param unknown $special_id
     */
    public function getServerForSpecialId($g,$special_id){
    	$sql = "SELECT s.id,s.server_name,s.develop_id,s.server_num FROM server as s INNER JOIN special_servicer as ss ON ss.server_id = s.id WHERE s.game = '{$g}' and ss.special_id = {$special_id} order by s.server_num desc";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    /**
     * 根据$special_id 服id获取服务器
     * @param unknown $special_id
     */
    public function getServerForSpeNum($g,$special_id,$server_num){
    	$sql = "SELECT s.id,s.server_name,s.develop_id,s.server_num FROM server as s INNER JOIN special_servicer as ss ON ss.server_id = s.id WHERE s.game = '{$g}' and s.server_num = {$server_num} and ss.special_id = {$special_id}";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    /**
     * 根据ids查询出服务器列表
     */
    public function getServerForIds($g,$ids){
    	$sql = "select id,game,server_name,server_num,develop_id,server_name,ip,port,show_status,float_status,open_time,name,is_wlk,version_update FROM server where id in ({$ids})";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    /**
     * 根据ids查询出服务器列表,专服信息
     */
    public function getServerSpeForIds($g,$ids){
    	$sql = "select s.id,s.game,s.server_name,s.server_num,s.develop_id,s.server_name,s.ip,s.port,s.show_status,s.float_status,s.open_time,s.name,s.is_wlk,s.version_update,spe.api_synchro_url ";
        $sql .= "FROM server as s INNER JOIN special_servicer as ss ON ss.server_id = s.id INNER JOIN special_server as spe ON spe.id = ss.special_id ";
        $sql .= "where s.id in ({$ids})";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    /**
     * 用于意见反馈接口查询服务器
     * @param unknown $name
     * @param unknown $channel_id
     */
    public function getIDForNameChannel($name,$channel_id){
    	$sql = "SELECT s.id,s.name,sc.channel_id from `server` as s  INNER JOIN server_channel as sc ON sc.server_id = s.id WHERE name = '{$name}' AND sc.channel_id = {$channel_id}";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result; 	
    }
    
    
    /**
     * 根据ids查询出服务器列表(通过开服时间进行排序的)
     */
    public function getOrderServerForIds($g,$ids){
    	$sql = "select id,game,server_name,server_num,develop_id,server_name,ip,port,show_status,float_status,open_time,name,is_wlk,version_update FROM server where id in ({$ids}) order by open_time asc";
    	$args = array();
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    /**
     * 根据ids查询出服务器列表
     */
    public function getServerForNorinIds($g,$ids){
    	$sql = "select id,game,server_name,server_num,develop_id,server_name,ip,port,show_status,float_status,open_time,name,is_wlk,version_update,check_flag FROM server where id not in ({$ids}) and game = '{$g}'";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }

} 